﻿using BusinessLayer.DatabaseEntities;
using BusinessLayer.VDataEntities;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace BusinessLayer
{
    public class PromoterBusiness
    {
        public static void AddPromoter(string chineseName, string userName)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {
                var duplicateCount = (from p in entities.Promoter
                                      where p.UserName.ToUpper() == userName.ToUpper()
                                      select p.ID).Count();
                if (duplicateCount == 0)
                {

                    Promoter promoter = new Promoter();
                    promoter.ChineseName = chineseName;
                    promoter.IsActive = true;
                    promoter.UserName = userName;
                    entities.Promoter.Add(promoter);
                    entities.SaveChanges();
                }
                else
                { throw new Exception("Duplicate User"); }
            }
        }

        public static void AddLocationToPromoter(long promoterID, long provinceID)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {
                RPromoterProvince pp = new RPromoterProvince();
                pp.ProvinceID = provinceID;
                pp.PromoterID = promoterID;
                pp.IsActive = true;
                entities.RPromoterProvince.Add(pp);
                entities.SaveChanges();
            }
        }

        public static void DeleteLocationToPromoter(long promoterID, long provinceID)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {
                SqlParameter paraPromoterID = new SqlParameter("@promoterID", promoterID);
                SqlParameter paraProvinceID = new SqlParameter("@provinceID", provinceID);
                entities.Database.ExecuteSqlCommand("Delete From RPromoterProvince where PromoterID = @promoterID and ProvinceID =@provinceID", paraPromoterID, paraProvinceID);

            }
        }

        public static VPromoterProvinceMappings[] GetPromoterProvincMappings(string name)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {

                var promoterProvinceInfo = from pp in entities.RPromoterProvince
                                           join province in entities.Province
                                           on pp.ProvinceID equals province.ID
                                           select new { pp.PromoterID, province.Name, province.ID };


                var promoterCityMappings = from p in entities.Promoter
                                           where ((p.ChineseName != null && p.ChineseName.Contains(name)) || p.UserName.Contains(name))
                                           join ppi in promoterProvinceInfo
                                          on p.ID equals ppi.PromoterID into temp
                                           from pcm in temp.DefaultIfEmpty()
                                           select new { p.ChineseName, p.UserName, p.ID, ProvinceInfo = pcm, p.IsActive };


                var promoterProvinceMappingGroups = from pcm in promoterCityMappings
                                                    group pcm by new { pcm.ID, pcm.ChineseName, pcm.UserName, pcm.IsActive } into g
                                                    select g;

                List<VPromoterProvinceMappings> vpcmList = new List<VPromoterProvinceMappings>();
                foreach (var ppmg in promoterProvinceMappingGroups)
                {
                    VPromoterProvinceMappings vppm = new VPromoterProvinceMappings() { Promoter = new VPromoter() { ChineseName = ppmg.Key.ChineseName, ID = ppmg.Key.ID, Name = ppmg.Key.UserName, IsActive = ppmg.Key.IsActive } };
                    List<VDataEntity> provinceList = new List<VDataEntity>();

                    foreach (var value in ppmg)
                    {
                        if (value.ProvinceInfo != null)
                        {
                            provinceList.Add(new VDataEntity() { ID = value.ProvinceInfo.ID, Name = value.ProvinceInfo.Name });
                        }
                    }

                    vppm.Provinces = provinceList.ToArray();
                    vpcmList.Add(vppm);
                }

                return vpcmList.ToArray();

            }
        }

        /// <summary>
        /// if the promoter is not validated, return null;
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static VPromoter ValidatePromoter(string name)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {
                var validatedPromoter = from promoter in entities.Promoter
                                        where promoter.UserName == name
                                        && promoter.IsActive == true
                                        select new VPromoter() { ChineseName = promoter.ChineseName, Name = promoter.UserName, IsActive = true, ID = promoter.ID };
                return validatedPromoter.FirstOrDefault();
            }
        }


        public static VPromoterProvinceMappings GetProvinceMappingsByPromoterID(long promoterID)
        {
            using (TrialManagementEntities entities = new TrialManagementEntities())
            {
                var promoter = (from p in entities.Promoter
                                where p.ID == promoterID
                                select new VPromoter() { ChineseName = p.ChineseName, ID = p.ID, IsActive = p.IsActive, Name = p.UserName }).FirstOrDefault();
                var provinces = (from rpc in entities.RPromoterProvince
                                 join province in entities.Province on rpc.ProvinceID equals province.ID
                                 where rpc.PromoterID == promoterID && rpc.IsActive == true
                                 select new VDataEntity() { ID = province.ID, Name = province.Name }).ToArray();
                VPromoterProvinceMappings vppm = new VPromoterProvinceMappings() { Provinces = provinces, Promoter = promoter };

                return vppm;
            }
        }

        public static void PromoterStatusUpdate(long promoterID)
        {
            using (TrialManagementEntities entites = new TrialManagementEntities())
            {
                var promoter = (from p in entites.Promoter where p.ID == promoterID select p).FirstOrDefault();
                if (promoter != null)
                {
                    bool status = promoter.IsActive;
                    promoter.IsActive = !status;
                    entites.SaveChanges();
                }
            }
        }
    }
}
